package com.dshore.messagecenter.dao.basedata;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.dshore.messagecenter.model.basedata.MdApp;
import com.dshore.messagecenter.model.basedata.MdAppType;
import com.dshore.messagecenter.model.topicqueue.MdTopicAccess;
import com.dshore.messagecenter.model.topicqueue.MdTopicSend;
import com.dshore.messagecenter.util.UUIDUtil;


@Repository
public class MdAppDao {
	@Autowired
	JdbcTemplate jdbcTemplate;
	//查询总数
	public Long queryCountMcAPP(MdApp mcAPP) {
		StringBuffer sql = new StringBuffer();
		List<Object>params=new ArrayList<Object> ();
		sql.append("select count(*) from T_MD_APP where del_flag=0");
		if(StringUtils.isNotBlank(mcAPP.getId())) {
			sql.append(" and id=?");
			params.add(mcAPP.getId());
		}
		if(StringUtils.isNotBlank(mcAPP.getAppCode())) {
			sql.append(" and app_code LIKE ? ");
			params.add("%"+mcAPP.getAppCode().trim()+"%");
		}
		if(StringUtils.isNotBlank(mcAPP.getAppName())) {
			sql.append(" and app_name LIKE ? ");
			params.add("%"+mcAPP.getAppName().trim()+"%");
		}
		if(StringUtils.isNotBlank(mcAPP.getAppType())) {
			sql.append(" and APP_TYPE= ? ");
			params.add(mcAPP.getAppType());
		}
		if(StringUtils.isNotBlank(mcAPP.getInternetType())) {
			sql.append(" and Internet_type= ? ");
			params.add(mcAPP.getInternetType());
		}
		Object[] param=params.toArray();
		if(params.size()==0) {
			return jdbcTemplate.queryForObject(sql.toString(), Long.class);
		}else {
			return jdbcTemplate.queryForObject(sql.toString(), param, Long.class);
		}
	}
	//分页
	public List<MdApp> queryListMcAPP(MdApp mcAPP) {
		StringBuffer sql = new StringBuffer();
		List<Object> params=new ArrayList<Object> ();
		sql.append("select T_MD_APP.id,T_MD_APP.app_name,T_MD_APP.app_code,T_SYS_DICT.DICT_name "
				+ " status_name,T_MD_APP.status,T_MD_APP.CREATED_TIME,T_MD_APP.PRINCIPAL,"
				+ "T_MD_APP.PRINCIPAL_CONTACT,T_MD_APP.INTERNET_TYPE from T_MD_APP"
				+ " left join T_SYS_DICT ON T_MD_APP.status = T_SYS_DICT.DICT_code\r\n" + 
				"where  T_MD_APP.del_flag=0 and T_SYS_DICT.dict_type='app_status'");
		if(StringUtils.isNotBlank(mcAPP.getId())) {
			sql.append(" and T_MD_APP.id= ?");
			params.add(mcAPP.getId());
		}
		if(StringUtils.isNotBlank(mcAPP.getAppCode())) {
			sql.append(" and T_MD_APP.APP_code like  ? ");
			params.add("%"+mcAPP.getAppCode().trim()+"%");
		}
		if(StringUtils.isNotBlank(mcAPP.getAppName())) {
			sql.append(" and T_MD_APP.APP_name like  ? ");
			params.add("%"+mcAPP.getAppName().trim()+"%");
		}
		if(StringUtils.isNotBlank(mcAPP.getAppType())) {
			sql.append(" and T_MD_APP.APP_TYPE= ? ");
			params.add(mcAPP.getAppType());
		}
		if(StringUtils.isNotBlank(mcAPP.getInternetType())) {
			sql.append(" and T_MD_APP.Internet_type= ? ");
			params.add(mcAPP.getInternetType());
		}
		sql.append(" ORDER BY T_MD_APP.CREATED_TIME DESC" ); 
		 String sql1=" select * from ( select A.*,  ROWNUM AS RN,T_SYS_DICT.DICT_NAME"
		 		+ " internetName from( " + sql + " ) A LEFT JOIN T_SYS_DICT "
		 		+ "ON A.INTERNET_TYPE = T_SYS_DICT.DICT_code "
		 		+ "where  ROWNUM<= ? AND T_SYS_DICT.dict_type ='internet_type')"
		 		+ "where rn > ? order by CREATED_TIME desc"; 
		 Integer mun=mcAPP.getLimit()+mcAPP.getOffset();
		 Integer Offset=mcAPP.getOffset();
		 params.add(mun);
		 params.add(Offset);
		 Object[] param=params.toArray();
		 RowMapper<MdApp> mcAPPRowMapper = new BeanPropertyRowMapper<>(MdApp.class);
		 return jdbcTemplate.query(sql1, param, mcAPPRowMapper);
	}
	//查询应用分类（三集五大）
	public List<MdAppType> selectByAppType() {
		String sql = "select id,dict_name , dict_code from T_sys_dict where dict_type ='app_type'"
				+ " ORDER BY SEQ";
		RowMapper<MdAppType> mdAppTypeRowMapper = new BeanPropertyRowMapper<>(MdAppType.class);
		return jdbcTemplate.query(sql, mdAppTypeRowMapper);
	}
	//查询网络状态（外网/内网)
	public List<MdApp> selectByInternetType() {
		String sql = "select DICT_name internetName, DICT_code internetType from T_SYS_DICT where dict_type ='internet_type' ORDER BY SEQ";
		RowMapper<MdApp> mcAPPRowMapper = new BeanPropertyRowMapper<>(MdApp.class);
		return jdbcTemplate.query(sql, mcAPPRowMapper);
	}
	//新增（删减版）
	public Integer insertMcAPP(MdApp mcAPP) {
		String sql= "insert into T_MD_APP (id,INTERNET_TYPE, APP_name, APP_code,app_type,"
				+ "SERVICE_PUB_KEY,SERVICE_PRI_KEY,QUEUE_KEY,PRINCIPAL,PRINCIPAL_CONTACT,STATUS,"
				+ "CREATED_TIME,DEL_FLAG,MODIFIED_TIME) values "
				+ "(?, ?, ?, ?, ?, ?,?,?,?, ?, ?, ?, ?, ?)";
		return jdbcTemplate.update(sql, UUIDUtil.getUUId(),mcAPP.getInternetType(),mcAPP.getAppName().trim(),
				mcAPP.getAppCode(),mcAPP.getAppType(),mcAPP.getServicePubKey(),mcAPP.getServicePriKey(),
				mcAPP.getQueueKey(),mcAPP.getPrincipal().trim(),mcAPP.getPrincipalContact().trim(),
				"1002",new Date(),0,new Date());
	}
	//新增时查询appcode是否唯一（删减版）
	public Integer selectByAppCode(String AppCode) {
		String sql= "select COUNT(1) from T_MD_APP where app_code=?";
		return jdbcTemplate.queryForObject(sql, Integer.class,AppCode);
	}
	//新增时查询appcode是否唯一（删减版）
	public Integer selectByAppName(String AppName) {
		String sql= "select COUNT(1) from T_MD_APP where App_name=?";
		return jdbcTemplate.queryForObject(sql, Integer.class,AppName);
	}
	//修改前查询根据id查询是否存在
	public Integer getByIdOfNum(String id) {
		String sql = "select COUNT(1) from T_MD_APP where ID = ?";
		return jdbcTemplate.queryForObject(sql, Integer.class,id);
	}
	//修改前查询根据id查询是否存在
	public String getByIdOfCode(String id) {
		String sql = "select APP_CODE from T_MD_APP where ID =?";
		return jdbcTemplate.queryForObject(sql,String.class,id);
	}
	//修改前根据id查询
	public MdApp getById(String id) {
		String sql = "select id, APP_name, APP_code,PRINCIPAL,PRINCIPAL_CONTACT,APP_TYPE,"
				+ "INTERNET_TYPE from T_MD_APP where id = ?";
		RowMapper<MdApp> mcAPPRowMapper = new BeanPropertyRowMapper<>(MdApp.class);
		return jdbcTemplate.queryForObject(sql, mcAPPRowMapper, id);
	}
	//修改
	public Integer modify(MdApp mcAPP) {
		String sql = "update T_MD_APP set APP_name = ?,APP_code=?,PRINCIPAL=?,PRINCIPAL_CONTACT=?,"
				+ "APP_TYPE=?,INTERNET_TYPE=? ,MODIFIED_TIME=? where id = ?";
		return jdbcTemplate.update(sql,mcAPP.getAppName().trim(),mcAPP.getAppCode().trim(),
				mcAPP.getPrincipal().trim(),mcAPP.getPrincipalContact().trim(),mcAPP.getAppType(),
				mcAPP.getInternetType(),new Date(),mcAPP.getId());
	}
	//更新密钥
	public Integer updatekey(String pubkey,String id,String key,String priKey) {
		String sql = "update T_MD_APP set service_PUB_key=? ,MODIFIED_TIME=?, QUEUE_KEY=?,SERVICE_PRI_KEY=?   "
				+ "where id = ?";
		return jdbcTemplate.update(sql,pubkey,new Date(),key,priKey,id);
	}
	//根据id查询队列密钥
	public MdApp selectByqueuekey(String id) {
		String sql = "select queue_key,app_code,app_name from T_MD_APP where id = ?";
		RowMapper<MdApp> mcAPPRowMapper = new BeanPropertyRowMapper<>(MdApp.class);
		return jdbcTemplate.queryForObject(sql, mcAPPRowMapper, id);
	}
	//根据id查询服务密钥
	public MdApp selectByservicekey(String id) {
		String sql = "select service_PUB_key,app_name from T_MD_APP where id = ?";
		RowMapper<MdApp> mcAPPRowMapper = new BeanPropertyRowMapper<>(MdApp.class);
		return jdbcTemplate.queryForObject(sql, mcAPPRowMapper, id);
	}
	//批量删除
	public int[] deleteAll(String[] ids) {
	   StringBuffer sql = new StringBuffer();
	   final List<Object> updateParams=new ArrayList<>();
	   for(int i=0;i<ids.length;i++) {
		   String id=ids[i];
		   updateParams.add(id);
		}
		sql.append("update T_MD_APP set del_flag = 1,modified_time=? where id = ? ");
		return jdbcTemplate.batchUpdate(sql.toString(),new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
				ps.setString(2, updateParams.get(i).toString());
			}
			@Override
			public int getBatchSize() {
				return updateParams.size();
			}
		});
	}
	//批量主题队列
	public List<Map<String,Object>> selectAll(String[] ids) {
		StringBuffer sql=new StringBuffer("select queue_key,id,app_code from T_MD_APP where "
				+ "id in ( ");
		for(int i=0;i<ids.length;i++) {
			sql.append(" ? ");
			if(i!=ids.length-1) {
				sql.append(" , ");
			}
		}
		sql.append(" ) ");
		Object[] param=ids;
		return jdbcTemplate.queryForList(sql.toString(), param);
	}
	//校验应用名称
	public List<MdApp> checkName(String appName) {
		String sql = "select ID from T_MD_APP where del_flag=0 and app_name=?";
		RowMapper<MdApp> mcAPPRowMapper = new BeanPropertyRowMapper<>(MdApp.class);
		return jdbcTemplate.query(sql, mcAPPRowMapper,appName.trim());
	}
	//应用编码
	public List<MdApp> checkCode() {
		String sql = "select * from (SELECT APP_CODE from T_MD_APP ORDER BY CREATED_TIME desc)"
				+ " where ROWNUM=1";
		RowMapper<MdApp> mcAPPRowMapper = new BeanPropertyRowMapper<>(MdApp.class);
		return jdbcTemplate.query(sql, mcAPPRowMapper);
	}
	//查询根节点和子节点
	public List<MdAppType> selectByParentTree() {
		String sql = "select ID,dict_name dictName,dict_code dictCode from T_sys_dict where"
				+ " dict_type='app_type' ORDER BY SEQ";
		RowMapper<MdAppType> mcAPPRowMapper = new BeanPropertyRowMapper<>(MdAppType.class);
		return jdbcTemplate.query(sql, mcAPPRowMapper);
	}
	//查询树状图的叶子节点
	public List<MdApp> selectByTree() {
		String sql = "select ID,APP_NAME,APP_TYPE from T_MD_APP where del_flag = 0";
		RowMapper<MdApp> mcAPPRowMapper = new BeanPropertyRowMapper<>(MdApp.class);
		return jdbcTemplate.query(sql, mcAPPRowMapper);
	}
	
	//删除时查询主题队列发送表是否含目标应用
	public List<MdTopicSend> selectBySendApp(String targetAppidt) {
		String sql = "select TOPIC_QUEUE_NO from T_MD_TOPIC_SEND where del_flag = 0 AND"
				+ " TARGET_APPID=?";
		RowMapper<MdTopicSend> mcAPPRowMapper = new BeanPropertyRowMapper<>(MdTopicSend.class);
		return jdbcTemplate.query(sql, mcAPPRowMapper,targetAppidt);
	}
	//删除时查询主题队列发送表是否含源应用
	public List<MdTopicAccess> selectByAccessApp(String targetAppidt) {
		String sql = "select A.TOPIC_QUEUE_NO,T.id TopicId from T_MD_TOPIC_ACCESS A left join" + 
				" T_MD_TOPIC T on A.TOPIC_QUEUE_NO=T.TOPIC_QUEUE_NO" + 
				" where T.del_flag = 0 AND A.ACCESS_APPID=?";
		RowMapper<MdTopicAccess> mcAPPRowMapper = new BeanPropertyRowMapper<>(MdTopicAccess.class);
		return jdbcTemplate.query(sql, mcAPPRowMapper,targetAppidt);
	}
	//重置时查询主题队列发送表是否含目标应用（已发布的）
	public Integer updateKeyBySendApp(String targetAppidt) {
		String sql ="select count(1) from T_MD_TOPIC_SEND LEFT JOIN T_MD_TOPIC ON "
				  + "T_MD_TOPIC.TOPIC_QUEUE_NO=T_MD_TOPIC_SEND.TOPIC_QUEUE_NO "+
				   "where T_MD_TOPIC_SEND.del_flag = 0 AND T_MD_TOPIC_SEND.TARGET_APPID=? "
				+ " AND T_MD_TOPIC.STATUS='1302'";
		return jdbcTemplate.queryForObject(sql.toString(),Integer.class,targetAppidt);
	}
	//重置时查询主题队列发送表是否含源应用（已发布的）
	public Integer updateKeyByAccessApp(String targetAppidt) {
		String sql ="select count(1) from T_MD_TOPIC_ACCESS A left join " +
				" T_MD_TOPIC T on A.TOPIC_QUEUE_NO=T.TOPIC_QUEUE_NO"+
				" where T.del_flag = 0 AND A.ACCESS_APPID=? and T.status='1302'";
		return jdbcTemplate.queryForObject(sql.toString(),Integer.class,targetAppidt);
	}
}
